package db;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.eclipse.swt.SWT;
import org.eclipse.swt.widgets.MessageBox;

import parsing.ParsingCitiesToTextB;
import parsing.ParsingTools;
import ui.Logs;
import ui.MainWindow;
import core.City;
import core.Continent;
import core.Country;
import core.Group;
import core.QuestionLog;
import core.User;

public class DB_SQL extends DB_Abstract {

	// ################### Connection Variables
	// ##################################

	private Connection conn = null;
	private Statement stmt;
	private PreparedStatement pstm;
	private ResultSet rs = null;

	// DBMysql11 Configurations
	public String port = "3305";
	public String host_addr = "127.0.0.1";
	public String schema_name ="DbMysql11";
	public String username = "DbMysql11";
	public String password = "DbMysql11";

	// ################### Constructors ###################################

	// EmptyConstructor
	public DB_SQL() {

	}

	// ################### Overridden Connection Functions ################

	@Override
	public int connect() {

		// Initialize Connection
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println("Failed To Connect. stack trace:");
			e.printStackTrace();
			return 0;
		}

		// Connect To Scheme
		try {
			conn = DriverManager.getConnection("jdbc:mysql://" + host_addr
					+ ":" + port + "/" + schema_name, username, password);
		} catch (SQLException e) {
			System.out.println("Failed To Connect. stack trace:");
			e.printStackTrace();
			return 0;
		}

		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			System.out.println("Failed To Connect. stack trace:");
			e.printStackTrace();
			return 0;
		}

		System.out.println("DB Connection Successful.");
		return 1;
	}

	@Override
	public int closeConnection() {
		// Closing Connections

		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}

		try {
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}

		System.out.println("DB Connection Closed.");
		return 1;
	}

	// ################### Overridden SQL Functions #####################

	@Override
	public ResultSet execQuery(String query) {

		// Using Regular Query
		try {
			rs = stmt.executeQuery(query);

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}

		return rs;
	}

	@Override
	public int execUpdate(String update) {
		// Using Regular Query
		try {
			stmt.executeUpdate(update);

		} catch (SQLException e) {
			
			e.printStackTrace();
			return 0;
		}

		return 1;
	}

	@Override
	public int updateFromString(String query) {

		Statement stmt = DB_Tools.String2Statements(query, getStmt());

		if (execStatment(stmt) == 0) {
			System.out.println("Query Failed");
			return 0;
		} else {
			System.out.println("Query Succeeded");
		}

		return 1;

	}

	@Override
	public int updateFromFile(String filePath) {
		String query = ParsingTools.File2String(filePath);
		return updateFromString(query);
	}

	@Override
	public int execStatment(Statement stmt) {

		try {
			stmt.executeBatch();
		} catch (SQLException e) {
			
			e.printStackTrace();
			return 0;
		}

		return 1;
	}

	// ################################# Overridden Basic DB Functions
	// #######################################

	// ################### Add Data Functions
	// ###################################

	// Add DB
	@Override
	public int addNewContinent(Continent newContinent) {
		try {
			String table_name = SQL_Schema.Continent.TABLE_NAME;
			String continent_id_str = SQL_Schema.Continent.ContinentID;
			String countinent_name = SQL_Schema.Continent.ContinentName;

			String insertCommand = "INSERT INTO " + table_name + " ("
					+ countinent_name + "," + continent_id_str + ")";

			pstm = conn.prepareStatement(insertCommand + " VALUES (?, ?);");
			pstm.setString(1, newContinent.getContinentName());
			pstm.setInt(2, newContinent.getContinentId());

			pstm.addBatch();
			pstm.executeBatch();

		} catch (SQLException e) {
			
			e.printStackTrace();
			return 0;
		}

		return 1;
	}

	@Override
	public int addNewCountry(Country newCountry) {
		try {
			String table_name = SQL_Schema.Countries.TABLE_NAME;
			String country_id_str = SQL_Schema.Countries.CountryID;
			String cont_id_str = SQL_Schema.Countries.ContinnentID;
			String country_name = SQL_Schema.Countries.CountryName;

			String insertCommand = "INSERT INTO " + table_name + " ("
					+ country_name + "," + country_id_str + "," + cont_id_str
					+ ")";

			pstm = conn.prepareStatement(insertCommand + " VALUES (?, ?, ?);");
			pstm.setString(1, newCountry.getCountryName());
			pstm.setInt(2, newCountry.getCountryId());
			pstm.setInt(3, newCountry.getContinentId());

			pstm.addBatch();
			pstm.executeBatch();

		} catch (SQLException e) {
			
			e.printStackTrace();
			return 0;
		}

		return 1;
	}

	@Override
	public int addNewCity(City newCity) {
		try {
			String table_name = SQL_Schema.Cities.TABLE_NAME;
			String city_name_str = SQL_Schema.Cities.CityName;
			String lat_str = SQL_Schema.Cities.Lat;
			String lon_str = SQL_Schema.Cities.Lon;
			String country_id_str = SQL_Schema.Cities.CountryID;

			String insertCommand = "INSERT INTO " + table_name + " ("
					+ city_name_str + "," + country_id_str + "," + lat_str
					+ "," + lon_str + ")";

			pstm = conn.prepareStatement(insertCommand
					+ " VALUES (?, ?, ? ,?);");
			pstm.setString(1, newCity.getCityName());
			pstm.setInt(2, newCity.getCountryId());
			pstm.setFloat(3, newCity.getLatitude());
			pstm.setFloat(4, newCity.getLongtitude());

			pstm.addBatch();
			pstm.executeBatch();

		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}

		return 1;
	}

	// Add Users Data
	@Override
	public int addNewUser(User user) {
		try {
			String table_name = SQL_Schema.User.TABLE_NAME;
			String user_name_str = SQL_Schema.User.UserNAME;
			String pass_str = SQL_Schema.User.Password;

			String insertCommand = "INSERT INTO " + table_name + " ("
					+ user_name_str + "," + pass_str + ")";

			pstm = conn.prepareStatement(insertCommand + " VALUES (?, ?);");
			pstm.setString(1, user.getName());
			pstm.setString(2, user.getPassword());

			pstm.addBatch();
			pstm.executeBatch();

		} catch (SQLException e) {
			
			e.printStackTrace();
			return 0;
		}

		return 1;
	}

	@Override
	public int addNewGroup(Group group) {
		try {
			String table_name = SQL_Schema.Groups.TABLE_NAME;
			String name_str = SQL_Schema.Groups.groupNAME;
			String continent_str = SQL_Schema.Groups.continentID;
			String country_str = SQL_Schema.Groups.countryID;
			String numQuestions_str = SQL_Schema.Groups.numQuestions;

			String countryID = (group.getCountryID() > -1) ? Integer
					.toString(group.getCountryID()) : null;
			String continentID = (group.getContID() > -1) ? Integer
					.toString(group.getContID()) : null;

			String insertCommand = "INSERT INTO " + table_name + " ("
					+ name_str + "," + numQuestions_str + "," + country_str
					+ "," + continent_str + ")";

			pstm = conn.prepareStatement(insertCommand
					+ " VALUES (?, ?, ?, ?);");
			pstm.setString(1, group.getName());
			pstm.setInt(2, group.getNumOfQuestions());
			pstm.setString(3, countryID);
			pstm.setString(4, continentID);

			pstm.addBatch();
			pstm.executeBatch();

		} catch (SQLException e) {
			
			e.printStackTrace();
			return 0;
		}

		return 1;
	}

	@Override
	public int addUserToGroup(int userID, int groupID) {
		try {
			String table_name = SQL_Schema.user_in_group.TABLE_NAME;
			String user_id_str = SQL_Schema.user_in_group.UserID;
			String group_id_str = SQL_Schema.user_in_group.GroupID;
			String score_str = SQL_Schema.user_in_group.SCORE;

			String insertCommand = "INSERT INTO " + table_name + " ("
					+ user_id_str + "," + group_id_str + "," + score_str + ")";

			pstm = conn.prepareStatement(insertCommand + " VALUES (?, ?, ?);");
			pstm.setInt(1, userID);
			pstm.setInt(2, groupID);
			pstm.setInt(3, 0);

			pstm.addBatch();
			pstm.executeBatch();

		} catch (SQLException e) {
			
			e.printStackTrace();
			return 0;
		}

		return 1;
	}

	// ################### Remove Data Functions
	// ################################

	// Removes DB
	@Override
	public int removeCountry(int ID) {
		String table_name = SQL_Schema.Countries.TABLE_NAME;
		String fieldID_Str = SQL_Schema.Countries.CountryID;
		return removeFieldByID(table_name, fieldID_Str, ID);
	}

	@Override
	public int removeCity(int cityID) {
		String table_name = SQL_Schema.Cities.TABLE_NAME;
		String fieldID_Str = SQL_Schema.Cities.CityID;
		return removeFieldByID(table_name, fieldID_Str, cityID);
	}

	// Removes Users Data
	@Override
	public int removeGroup(int groupID) {
		String table_name = SQL_Schema.Groups.TABLE_NAME;
		String fieldID_Str = SQL_Schema.Groups.groupID;
		return removeFieldByID(table_name, fieldID_Str, groupID);
	}

	@Override
	public int removeUser(User user) {

		String table_name = SQL_Schema.User.TABLE_NAME;
		String name_Str = SQL_Schema.User.UserNAME;
		return removeFieldByName(table_name, name_Str, user.getName());
	}

	@Override
	public int removeUserFromGroup(int userID, int groupID) {
		String table_name = SQL_Schema.user_in_group.TABLE_NAME;
		String user_Str = SQL_Schema.user_in_group.UserID;
		String group_Str = SQL_Schema.user_in_group.GroupID;

		return removeFieldByTwoIDs(table_name, user_Str, group_Str, userID,
				groupID);
	}

	// ################### Modify Data Functions
	// ################################

	// Modify DB
	public int modifyContinent(Continent cont, String newName) {
		// Assume can only Modify a country name
		try {
			String table_name = SQL_Schema.Continent.TABLE_NAME;
			String cont_id_str = SQL_Schema.Continent.ContinentID;
			String cont_name = SQL_Schema.Continent.ContinentName;

			String UPDATE = "Update " + table_name;
			String SET = " SET " + cont_name + "= ?";
			String WHERE = " WHERE " + cont_id_str + "= ?";

			String insertCommand = UPDATE + SET + WHERE;

			pstm = conn.prepareStatement(insertCommand + ";");
			pstm.setString(1, newName);
			pstm.setInt(2, cont.getContinentId());

			pstm.addBatch();
			pstm.executeBatch();

		} catch (SQLException e) {

			e.printStackTrace();
			return 0;
		}

		return 1;
	}

	@Override
	public int modifyCountry(Country count, String newName) {
		// Assume can only Modify a country name
		try {
			String table_name = SQL_Schema.Countries.TABLE_NAME;
			String country_id_str = SQL_Schema.Countries.CountryID;
			String country_name = SQL_Schema.Countries.CountryName;

			String UPDATE = "Update " + table_name;
			String SET = " SET " + country_name + "= ?";
			String WHERE = " WHERE " + country_id_str + "= ?";

			String insertCommand = UPDATE + SET + WHERE;

			pstm = conn.prepareStatement(insertCommand + ";");
			pstm.setString(1, newName);
			pstm.setInt(2, count.getCountryId());

			pstm.addBatch();
			pstm.executeBatch();

		} catch (SQLException e) {
			
			e.printStackTrace();
			return 0;
		}

		return 1;
	}

	@Override
	public int modifyCity(int cityID, String newName) {
		try {
			String table_name = SQL_Schema.Cities.TABLE_NAME;
			String city_name_str = SQL_Schema.Cities.CityName;
//			String lat_str = SQL_Schema.Cities.Lat;
//			String lon_str = SQL_Schema.Cities.Lon;
			String city_id_str = SQL_Schema.Cities.CityID;

			String UPDATE = "Update " + table_name;
			String SET = " SET " + city_name_str + "= ?";
			String WHERE = " WHERE " + city_id_str + "= ?";

			String insertCommand = UPDATE + SET + WHERE;

			pstm = conn.prepareStatement(insertCommand + ";");
			pstm.setString(1, newName);
			pstm.setInt(2, cityID);

			pstm.addBatch();
			pstm.executeBatch();

		} catch (SQLException e) {
			
			e.printStackTrace();
			return 0;
		}

		return 1;

	}

	// Modify city longitude
	@Override
	public int modifyCityLong(float longitude, int cityId) {
		try {
			String table_name = SQL_Schema.Cities.TABLE_NAME;
			String lon_str = SQL_Schema.Cities.Lon;
			String city_id_str = SQL_Schema.Cities.CityID;

			String UPDATE = "Update " + table_name;
			String SET = " SET " + lon_str + "= ?";
			String WHERE = " WHERE " + city_id_str + "= ?";

			String insertCommand = UPDATE + SET + WHERE;

			pstm = conn.prepareStatement(insertCommand + ";");
			pstm.setFloat(1, longitude);
			pstm.setInt(2, cityId);

			pstm.addBatch();
			pstm.executeBatch();

		} catch (SQLException e) {
			
			e.printStackTrace();
			return 0;
		}

		return 1;

	}

	// Modify city latitude
	@Override
	public int modifyCityLat(float latitude, int cityId) {
		try {
			String table_name = SQL_Schema.Cities.TABLE_NAME;
			String lat_str = SQL_Schema.Cities.Lat;
			String city_id_str = SQL_Schema.Cities.CityID;

			String UPDATE = "Update " + table_name;
			String SET = " SET " + lat_str + "= ?";
			String WHERE = " WHERE " + city_id_str + "= ?";

			String insertCommand = UPDATE + SET + WHERE;

			pstm = conn.prepareStatement(insertCommand + ";");
			pstm.setFloat(1, latitude);
			pstm.setInt(2, cityId);

			pstm.addBatch();
			pstm.executeBatch();

		} catch (SQLException e) {
			
			e.printStackTrace();
			return 0;
		}

		return 1;

	}

	// Modify Users Data
	@Override
	public int modifyGroup(int oldGroupID, Group newGroup) {

		try {
			String table_name = SQL_Schema.Groups.TABLE_NAME;
			String group_name = SQL_Schema.Groups.groupNAME;
			String group_num_question = SQL_Schema.Groups.numQuestions;
			String group_id = SQL_Schema.Groups.groupID;
			String country_id = SQL_Schema.Groups.countryID;
			String continent_id = SQL_Schema.Groups.continentID;

			String UPDATE = "Update " + table_name;
			String SET = " SET " + group_name + "= ?" + ","
					+ group_num_question + "= ? " + ", " + country_id + "= ? "
					+ ", " + continent_id + "= ?";
			String WHERE = " WHERE " + group_id + "= ?";

			String insertCommand = UPDATE + SET + WHERE;

			String countryID = (newGroup.getCountryID() > -1) ? Integer
					.toString(newGroup.getCountryID()) : null;
			String continentID = (newGroup.getContID() > -1) ? Integer
					.toString(newGroup.getContID()) : null;

			pstm = conn.prepareStatement(insertCommand + ";");
			pstm.setString(1, newGroup.getName());
			pstm.setInt(2, newGroup.getNumOfQuestions());
			pstm.setString(3, countryID);
			pstm.setString(4, continentID);
			pstm.setInt(5, oldGroupID);

			pstm.addBatch();
			pstm.executeBatch();

		} catch (SQLException e) {
			
			e.printStackTrace();
			return 0;
		}

		return 1;
	}

	@Override
	public int modifyUser(String oldName, User newUser) {

		try {
			String table_name = SQL_Schema.User.TABLE_NAME;
			String user_name = SQL_Schema.User.UserNAME;
			String user_pass = SQL_Schema.User.Password;
//			String user_id = SQL_Schema.User.UserID;

			String UPDATE = "Update " + table_name;
			String SET = " SET " + user_name + "= ?" + "," + user_pass + "= ?";
			String WHERE = " WHERE " + user_name + "= ?";

			String insertCommand = UPDATE + SET + WHERE;

			pstm = conn.prepareStatement(insertCommand + ";");
			pstm.setString(1, newUser.getName());
			pstm.setString(2, newUser.getPassword());
			pstm.setString(3, oldName);

			pstm.addBatch();
			pstm.executeBatch();

		} catch (SQLException e) {
			
			e.printStackTrace();
			return 0;
		}

		return 1;
	}

	@Override
	public int modifyUserInGroup(int userID, int groupID, int newScore) {
		try {
			String table_name = SQL_Schema.user_in_group.TABLE_NAME;
			String user_Str = SQL_Schema.user_in_group.UserID;
			String group_Str = SQL_Schema.user_in_group.GroupID;
			String score_str = SQL_Schema.user_in_group.SCORE;

			String UPDATE = "Update " + table_name;
			String SET = " SET " + score_str + "= ?";
			String WHERE = " WHERE " + user_Str + "= ? " + " and " + group_Str
					+ " = ? ";

			String insertCommand = UPDATE + SET + WHERE;

			pstm = conn.prepareStatement(insertCommand + ";");
			pstm.setInt(1, newScore);
			pstm.setInt(2, userID);
			pstm.setInt(3, groupID);

			pstm.addBatch();
			pstm.executeBatch();

		} catch (SQLException e) {
			
			e.printStackTrace();
			return 0;
		}

		return 1;
	}

	// #################### Get Data Functions
	// ##################################

	// ######Basics Functions

	@Override
	public City getCity(int cityID) {
		rs = getFieldByID(SQL_Schema.Cities.TABLE_NAME,
				SQL_Schema.Cities.CityID, cityID);
		return createCityFromRS(rs);
	}

	@Override
	public Country getCountry(int countryID) {
		rs = getFieldByID(SQL_Schema.Countries.TABLE_NAME,
				SQL_Schema.Countries.CountryID, countryID);
		return createCountryFromRS(rs);
	}

	@Override
	public Continent getContinent(int ContinentID) {
		rs = getFieldByID(SQL_Schema.Continent.TABLE_NAME,
				SQL_Schema.Continent.ContinentID, ContinentID);
		return createContinentFromRS(rs);
	}

	@Override
	public User getUser(String UserName) {
		rs = getFieldByName(SQL_Schema.User.TABLE_NAME,
				SQL_Schema.User.UserNAME, UserName);
		return createUserFromRS(rs);
	}

	@Override
	public Group getGroup(int groupID) {

		rs = getFieldByID(SQL_Schema.Groups.TABLE_NAME,
				SQL_Schema.Groups.groupID, groupID);
		return createGroupFromRS(rs);
	}

	// ###### Proprietary Functions

	// /Basics All Data

	@Override
	public LinkedList<Group> getGroups() {
		rs = getTable(SQL_Schema.Groups.TABLE_NAME);
		return createGroupsFromRS(rs);
	}

	@Override
	public LinkedList<Country> getCountriesSorted() {
		rs = getTableSorted(SQL_Schema.Countries.TABLE_NAME,
				SQL_Schema.Countries.CountryName);
		return createCountriesFromRS(rs);
	}

	@Override
	public LinkedList<Continent> getAllContinents() {

		rs = getTableSorted(SQL_Schema.Continent.TABLE_NAME,
				SQL_Schema.Continent.ContinentName);
		return createContinentsFromRS(rs);
	}

	// ###### Proprietary Functions

	// Users List
	@Override
	public LinkedList<Group> getGroupsInUser(int userID) {

		try {
			String query = "SELECT * FROM groups, usersInGroups"
					+ " WHERE usersInGroups.group_id = groups.group_id and "
					+ userID + " = usersInGroups.user_id";

			rs = stmt.executeQuery(query);

		} catch (SQLException e) {
			
			e.printStackTrace();
			return null;
		}

		return createGroupsFromRSWithScore(rs);
	}

	// Users List
	@Override
	public LinkedList<User> getUsersInGroupSorted(int groupID) {

		try {
			String query = "SELECT * FROM  usersInGroups, users where users.user_id = usersInGroups.user_id and usersInGroups.group_id="
					+ groupID + " order by score;";
			// String query =
			// "SELECT * FROM groups, usersInGroups, users where users.user_id = usersInGroups.user_id and groups.group_id="+groupID
			// + " order by score;";

			rs = stmt.executeQuery(query);

		} catch (SQLException e) {
			
			e.printStackTrace();
			return null;
		}

		return createSortedUsersFromRS(rs);

	}

	// Cities List
	@Override
	public LinkedList<City> getCitiesFromCountry(int countryID) {
		rs = getFieldByIDSorted(SQL_Schema.Cities.TABLE_NAME,
				SQL_Schema.Cities.CityName, SQL_Schema.Cities.CityID,
				SQL_Schema.Cities.CountryID, countryID);
		return createCitiesFromRS(rs);
	}

	// Score
	@Override
	public int getUserScoreInGroup(int userID, int groupID) {

		String table_name = SQL_Schema.user_in_group.TABLE_NAME;
		String group_id_str = SQL_Schema.user_in_group.GroupID;
		String user_id_str = SQL_Schema.user_in_group.UserID;

		rs = getFieldByTwoID(table_name, user_id_str, group_id_str, userID,
				groupID);
		return createScoreFromRS(rs);
	}

	// #################### SQL-QUERIES
	// ##########################################3

	// #################### Create Data From RS - Query #######

	// ####Continent
	// create country from all fields
	public Continent createContinentFromRS(ResultSet rs) {

		String cont_id_str = null;
		String cont_name = null;

		try {
			while (rs.next() == true) {

				cont_id_str = rs.getString(SQL_Schema.Continent.ContinentID);
				cont_name = rs.getString(SQL_Schema.Continent.ContinentName);

				return new Continent(cont_name, Integer.parseInt(cont_id_str));
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}

		return new Continent("-1", -1);

	}

	public LinkedList<Continent> createContinentsFromRS(ResultSet rs) {
		LinkedList<Continent> allContinents = new LinkedList<Continent>();

		String cont_id_str = null;
		String cont_name = null;

		try {
			while (rs.next() == true) {

				cont_id_str = rs.getString(SQL_Schema.Continent.ContinentID);
				cont_name = rs.getString(SQL_Schema.Continent.ContinentName);

				allContinents.add(new Continent(cont_name, Integer
						.parseInt(cont_id_str)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return allContinents;
	}

	// ####Country

	// create country from all fields
	public Country createCountryFromRS(ResultSet rs) {

		String cont_id_str = null;
		String country_id_str = null;
		String country_name = null;

		try {
			while (rs.next() == true) {

				country_id_str = rs.getString(SQL_Schema.Countries.CountryID);
				cont_id_str = rs.getString(SQL_Schema.Countries.ContinnentID);
				country_name = rs.getString(SQL_Schema.Countries.CountryName);

				return new Country(Integer.parseInt(cont_id_str),
						Integer.parseInt(country_id_str), country_name);
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}

		return new Country(-1, -1, "-1");

	}

	public LinkedList<Country> createCountriesFromRS(ResultSet rs) {

		LinkedList<Country> countries = new LinkedList<Country>();

		String cont_id_str = null;
		String country_id_str = null;
		String country_name = null;

		try {
			while (rs.next() == true) {

				country_id_str = rs.getString(SQL_Schema.Countries.CountryID);
				cont_id_str = rs.getString(SQL_Schema.Countries.ContinnentID);
				country_name = rs.getString(SQL_Schema.Countries.CountryName);
				countries.add(new Country(Integer.parseInt(cont_id_str),
						Integer.parseInt(country_id_str), country_name));
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}

		return countries;

	}

	// ####City
	// Create city from all fields
	public City createCityFromRS(ResultSet rs) {

		String city_id_str = null;
		String country_id_str = null;
		String city_name_str = null;
		String city_lat_str = null;
		String ciry_lon_str = null;

		int cityID, countryID;
		float lat, lon;

		try {
			while (rs.next() == true) {

				country_id_str = rs.getString(SQL_Schema.Cities.CountryID);
				city_id_str = rs.getString(SQL_Schema.Cities.CityID);
				city_name_str = rs.getString(SQL_Schema.Cities.CityName);
				city_lat_str = rs.getString(SQL_Schema.Cities.Lat);
				ciry_lon_str = rs.getString(SQL_Schema.Cities.Lon);

				lat = Float.parseFloat(city_lat_str);
				lon = Float.parseFloat(ciry_lon_str);
				cityID = Integer.parseInt(city_id_str);
				countryID = Integer.parseInt(country_id_str);

				return new City(city_name_str, lat, lon, countryID, cityID);
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}

		return new City("-1", -1, -1, -1, -1);

	}

	// ####USER

	// Create user from all Fields
	public User createUserFromRS(ResultSet rs) {

		String user_id_str = null;
		String user_name = null;
		String pass_str = null;

		try {
			while (rs.next() == true) {

				user_id_str = rs.getString(SQL_Schema.User.UserID);
				user_name = rs.getString(SQL_Schema.User.UserNAME);
				pass_str = rs.getString(SQL_Schema.User.Password);

				return new User(Integer.parseInt(user_id_str), user_name,
						pass_str);

			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}

		return new User(-1, "-1", "-1");

	}

	// sorted Users list in Group
	private LinkedList<User> createSortedUsersFromRS(ResultSet rs) {
		LinkedList<User> UsersSorted = new LinkedList<User>();

		String user_id_str = null;
		String user_name = null;
		String pass_str = null;
		int score;

		try {
			while (rs.next() == true) {

				user_id_str = rs.getString(SQL_Schema.User.UserID);
				user_name = rs.getString(SQL_Schema.User.UserNAME);
				pass_str = rs.getString(SQL_Schema.User.Password);
				score = rs.getInt(SQL_Schema.user_in_group.SCORE);

				UsersSorted.add(new User(Integer.parseInt(user_id_str),
						user_name, pass_str, score));

			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}

		return UsersSorted;
	}

	// #### Groups

	// Create Groups from all Fields
	public Group createGroupFromRS(ResultSet rs) {

//		String table_name = null;
		String name_str = null;
		String continent_str = null;
		String country_str = null;
		int numQuestions;
		int group_id;
		int countryID;
		int continentID;

		try {
			while (rs.next() == true) {

				name_str = rs.getString(SQL_Schema.Groups.groupNAME);
				continent_str = rs.getString(SQL_Schema.Groups.continentID);
				country_str = rs.getString(SQL_Schema.Groups.countryID);
				numQuestions = rs.getInt(SQL_Schema.Groups.numQuestions);
				group_id = rs.getInt(SQL_Schema.Groups.groupID);

				countryID = (country_str != null) ? Integer
						.parseInt(country_str) : -1;
				continentID = (continent_str != null) ? Integer
						.parseInt(continent_str) : -1;

				return new Group(group_id, name_str, numQuestions, countryID,
						continentID);

			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}

		return new Group(-1, "-1", -1, -1, -1);

	}

	// Create Groups List from all Fields
	public LinkedList<Group> createGroupsFromRS(ResultSet rs) {

		LinkedList<Group> groups = new LinkedList<Group>();

//		String table_name = null;
		String name_str = null;
		String continent_str = null;
		String country_str = null;
		int numQuestions;
		int group_id;
		int countryID;
		int continentID;
//		int score;

		try {
			while (rs.next() == true) {

				name_str = rs.getString(SQL_Schema.Groups.groupNAME);
				continent_str = rs.getString(SQL_Schema.Groups.continentID);
				country_str = rs.getString(SQL_Schema.Groups.countryID);
				numQuestions = rs.getInt(SQL_Schema.Groups.numQuestions);
				group_id = rs.getInt(SQL_Schema.Groups.groupID);

				countryID = (country_str != null) ? Integer
						.parseInt(country_str) : -1;
				continentID = (continent_str != null) ? Integer
						.parseInt(continent_str) : -1;

				Group newGroup = new Group(group_id, name_str, numQuestions,
						countryID, continentID);
				groups.add(newGroup);

			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}

		return groups;
	}

	// Create Cities from fields CityName and CityID
	public LinkedList<City> createCitiesFromRS(ResultSet rs) {

		LinkedList<City> citiesNew = new LinkedList<City>();
		String city_id_str = null;
		String city_name_str = null;
		String city_lat_str = null;
		String city_lon_str = null;
		String country_id_str = null;
		int cityID, countryID;
		float lat, lon;

		try {
			while (rs.next() == true) {

				city_id_str = rs.getString(SQL_Schema.Cities.CityID);
				city_name_str = rs.getString(SQL_Schema.Cities.CityName);
				city_lat_str = rs.getString(SQL_Schema.Cities.Lat);
				city_lon_str = rs.getString(SQL_Schema.Cities.Lon);
				country_id_str = rs.getString(SQL_Schema.Cities.CountryID);
				cityID = Integer.parseInt(city_id_str);
				countryID = Integer.parseInt(country_id_str);
				lat = Float.parseFloat(city_lat_str);
				lon = Float.parseFloat(city_lon_str);
				citiesNew.add(new City(city_name_str, lat, lon, countryID,
						cityID));
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}

		return citiesNew;
	}

	// Create Groups List from all Fields
	public LinkedList<Group> createGroupsFromRSWithScore(ResultSet rs) {

		LinkedList<Group> groups = new LinkedList<Group>();

//		String table_name = null;
		String name_str = null;
		String continent_str = null;
		String country_str = null;
		int numQuestions;
		int group_id;
		int countryID;
		int continentID, score;

		try {
			while (rs.next() == true) {

				name_str = rs.getString(SQL_Schema.Groups.groupNAME);
				continent_str = rs.getString(SQL_Schema.Groups.continentID);
				country_str = rs.getString(SQL_Schema.Groups.countryID);
				numQuestions = rs.getInt(SQL_Schema.Groups.numQuestions);
				group_id = rs.getInt(SQL_Schema.Groups.groupID);
				score = rs.getInt(SQL_Schema.user_in_group.SCORE);

				countryID = (country_str != null) ? Integer
						.parseInt(country_str) : -1;
				continentID = (continent_str != null) ? Integer
						.parseInt(continent_str) : -1;

				Group newGroup = new Group(group_id, name_str, numQuestions,
						countryID, continentID);
				newGroup.setScore(score);
				groups.add(newGroup);

			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}

		return groups;
	}

	// #### User_In_Group

	// get Score from score field
	private int createScoreFromRS(ResultSet rs) {
//		String table_name = SQL_Schema.user_in_group.TABLE_NAME;
//		String group_id_str = SQL_Schema.user_in_group.GroupID;
//		String user_id_str = SQL_Schema.user_in_group.UserID;

		try {
			while (rs.next() == true) {

				return rs.getInt(SQL_Schema.user_in_group.SCORE);

			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}

		return -1;
	}

	// #################### Remove Data From Query ###########

	// Remove Fields by ID
	public int removeFieldByID(String tableName, String fieldName, int ID) {
		try {
			pstm = conn.prepareStatement("DELETE FROM " + tableName + " WHERE "
					+ fieldName + "  =  " + ID + ";");
			pstm.addBatch();
			pstm.executeBatch();

		} catch (SQLException e) {
			
			e.printStackTrace();
			return 0;
		}

		return 1;
	}

	public int removeFieldByTwoIDs(String table_name, String user_Str,
			String group_Str, int userID, int groupID) {
		try {
			pstm = conn.prepareStatement("DELETE FROM " + table_name
					+ " WHERE " + user_Str + "  =  " + userID + " and "
					+ group_Str + " = " + groupID + ";");
			pstm.addBatch();
			pstm.executeBatch();

		} catch (SQLException e) {
			
			e.printStackTrace();
			return 0;
		}

		return 1;
	}

	// Remove Fields by Name
	public int removeFieldByName(String tableName, String fieldName, String name) {
		try {

			// Because not deleting by key, should use this syntax
			pstm = conn.prepareStatement("");
			pstm.addBatch("SET SQL_SAFE_UPDATES=0;");
			pstm = conn.prepareStatement("DELETE FROM " + tableName + " WHERE "
					+ fieldName + "  =  '" + name + "';");
			pstm.addBatch();
			pstm.addBatch("SET SQL_SAFE_UPDATES=1;");

			pstm.executeBatch();

		} catch (SQLException e) {
			
			e.printStackTrace();
			return 0;
		}

		return 1;
	}

	// #################### Get Data From RS - Query #########

	// Get Fields by ID
	/*
	 * public ResultSet getFieldByID(String tableName, String fieldName, int ID)
	 * {
	 */

	// #################### Get Data From RS - Query #########

	// Get Table
	public ResultSet getTable(String tableName) {
		try {
			String query = "SELECT * FROM " + tableName + ";";
			rs = stmt.executeQuery(query);

		} catch (SQLException e) {
			
			e.printStackTrace();
			return null;
		}

		return rs;
	}

	private ResultSet getTableSorted(String tableName, String fieldName) {
		try {
			String query = "SELECT * FROM " + tableName + " order by "
					+ fieldName + ";";
			rs = stmt.executeQuery(query);

		} catch (SQLException e) {
			
			e.printStackTrace();
			return null;
		}

		return rs;
	}

	// Get Fields by ID
	public ResultSet getFieldByID(String tableName, String fieldName, int ID) {
		try {
			String query = "SELECT * FROM " + tableName + " WHERE " + fieldName
					+ "  =  " + ID + ";";
			rs = stmt.executeQuery(query);

		} catch (SQLException e) {
			
			e.printStackTrace();
			return null;
		}

		return rs;

	}

	public ResultSet getFieldByTwoID(String table_name, String user_id_str,
			String group_id_str, int userID, int groupID) {
		try {
			String query = "SELECT * FROM " + table_name + " WHERE "
					+ user_id_str + "  =  " + userID + " and " + group_id_str
					+ " = " + groupID + ";";
			rs = stmt.executeQuery(query);

		} catch (SQLException e) {
			
			e.printStackTrace();
			return null;
		}

		return rs;
	}

	public ResultSet getFieldByIDSorted(String tableName,
			String fieldName_name, String fieldName_id, String cond, int id) {
		try {
			String query = "SELECT " + fieldName_name + "," + fieldName_id
					+ " FROM " + tableName + " WHERE " + cond + "  =  " + id
					+ " ORDER BY " + fieldName_name + ";";
			rs = stmt.executeQuery(query);

		} catch (SQLException e) {
			
			e.printStackTrace();
			return null;
		}

		return rs;
	}

	// Get Fields by Name
	public ResultSet getFieldByName(String tableName, String fieldName,
			String name) {
		try {
			String query = "SELECT * FROM " + tableName + " WHERE " + fieldName
					+ "  =  '" + name + "';";
			rs = stmt.executeQuery(query);

		} catch (SQLException e) {
			
			e.printStackTrace();
			return null;
		}

		return rs;

	}

	// ################### Setters and Getters
	// ###################################

	public Connection getConn() {
		return conn;
	}

	public void setConn(Connection conn) {
		this.conn = conn;
	}

	public void setStmt(Statement stmt) {
		this.stmt = stmt;
	}

	@Override
	public Statement getStmt() {
		return stmt;
	}


	// **************************questions***********************************

	public List<City> getRandomCities(int numOfQuestions) {

		rs = getRandFieldWithLimit(SQL_Schema.Cities.TABLE_NAME, numOfQuestions);
		return createCitiesFromRS(rs);

	}

	// returns a list of numOfQuestions cities from continent contID
	public List<City> getCitiesByContinent(int contID, int numOfQuestions) {
		rs = getRandFieldWithLimitByContinent(numOfQuestions, contID);
		return createCitiesFromRS(rs);
	}

	// returns a list of numOfQuestions cities from country countryID
	public List<City> getCitiesByCountry(int countryID, int numOfQuestions) {
		rs = getRandFieldWithLimitByCountry(SQL_Schema.Cities.TABLE_NAME,
				numOfQuestions, countryID);
		return createCitiesFromRS(rs);
	}

	private ResultSet getRandFieldWithLimit(String tableName, int limit) {
		try {
			String query = "SELECT * " + " FROM " + tableName
					+ " ORDER BY rand()" + " LIMIT " + limit + ";";
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			
			e.printStackTrace();
			return null;
		}
		return rs;
	}

	private ResultSet getRandFieldWithLimitByCountry(String tableName,
			int limit, int countryId) {
		try {
			String query = "SELECT * " + " FROM " + tableName + " WHERE "
					+ SQL_Schema.Cities.CountryID + "= " + countryId
					+ " ORDER BY rand()" + " LIMIT " + limit + ";";
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			
			e.printStackTrace();
			return null;
		}
		return rs;
	}

	private ResultSet getRandFieldWithLimitByContinent(int limit,
			int continentId) {
		try {
			String query = "SELECT * " + " FROM "
					+ SQL_Schema.Cities.TABLE_NAME + " WHERE "
					+ SQL_Schema.Cities.CountryID + " IN" + "(SELECT "
					+ SQL_Schema.Countries.CountryID + " FROM "
					+ SQL_Schema.Countries.TABLE_NAME + " WHERE "
					+ SQL_Schema.Countries.ContinnentID + " = " + continentId
					+ " )" + " ORDER BY rand()" + " LIMIT " + limit + ";";
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			
			e.printStackTrace();
			return null;
		}
		return rs;
	}

	public int addNewQuestion(int user_id, int group_id, int city_id,
			int score, int seconds, long time) {
		java.sql.Timestamp sqlDate = new java.sql.Timestamp(time);
		try {
			String insertCommand = "INSERT INTO "
					+ SQL_Schema.Questions.TABLE_NAME + " ( "
					+ SQL_Schema.Questions.userID + " , "
					+ SQL_Schema.Questions.groupID + " , "
					+ SQL_Schema.Questions.cityID + " , "
					+ SQL_Schema.Questions.score + " , "
					+ SQL_Schema.Questions.seconds + " , "
					+ SQL_Schema.Questions.time + " )";
			pstm = conn.prepareStatement(insertCommand
					+ " VALUES (? ,? ,? , ? , ? ,?);");
			pstm.setInt(1, user_id);
			pstm.setInt(2, group_id);
			pstm.setInt(3, city_id);
			pstm.setInt(4, score);
			pstm.setInt(5, seconds);
			pstm.setTimestamp(6, sqlDate);

			pstm.addBatch();
			pstm.executeBatch();
		} catch (SQLException e) {
			
			e.printStackTrace();
			return 0;
		}
		return 1;
	}

	public LinkedList<QuestionLog> getQuestionsByUser(int user_id) {
		try {
			String query = "SELECT * " + " FROM "
					+ SQL_Schema.Questions.TABLE_NAME + " WHERE "
					+ SQL_Schema.Questions.userID + " = " + user_id + ";";
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		return createQuestionsFromRS(rs);
	}

	public LinkedList<QuestionLog> createQuestionsFromRS(ResultSet rs) {
		LinkedList<QuestionLog> questionsNew = new LinkedList<QuestionLog>();
		String user_id_str = null;
		String group_id_str = null;
		String city_id_str = null;
		String score_str = null;
		String seconds_str = null;
		String time_str = null;

		int userID, groupID, cityID, score, seconds;

		try {
			while (rs.next() == true) {

				user_id_str = rs.getString(SQL_Schema.Questions.userID);
				group_id_str = rs.getString(SQL_Schema.Questions.groupID);
				city_id_str = rs.getString(SQL_Schema.Questions.cityID);
				score_str = rs.getString(SQL_Schema.Questions.score);
				seconds_str = rs.getString(SQL_Schema.Questions.seconds);
				time_str = rs.getString(SQL_Schema.Questions.time);
				userID = Integer.parseInt(user_id_str);
				groupID = Integer.parseInt(group_id_str);
				cityID = Integer.parseInt(city_id_str);
				score = Integer.parseInt(score_str);
				seconds = Integer.parseInt(seconds_str);

				questionsNew.add(new QuestionLog(userID, groupID, cityID,
						score, seconds, time_str));
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}

		return questionsNew;
	}

	/*
	 * creating citiesUpdate table from the file that was created on error
	 * returns 0 else 1
	 */
	public int createUpdateCitiesTable(String filepath) {
		try {
			if (dropUpdatesTable()==0){
				
				return 0;
			}
			String queryCreate = "create table citiesUpdate(countryID INT(11),city_name varchar(100),	"
					+ "latitude float,longitude float,city_id INT(11) NOT NULL auto_increment,"
					+ "	PRIMARY KEY(city_id),UNIQUE (city_name,countryID));";
			stmt.executeUpdate(queryCreate);
			//workspacePath=workspacePath+File.separator+citiesFile;
			//workspacePath = workspacePath.replaceAll("\\\\", "\\/");
			filepath=filepath.replaceAll("\\\\", "\\/");
			String queryInsert = "Load data local infile " + "'"
					+ filepath + "'"
					+ " into table citiesUpdate" + " Lines Terminated by '\n';";
			stmt.executeUpdate(queryInsert);
			String querySet = "set sql_safe_updates=0;";
			stmt.addBatch(querySet);
			String queryDelete = "delete from citiesUpdate where countryID=0";
			stmt.addBatch(queryDelete);
			stmt.executeBatch();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("Error");
			return 0;
		}
		return 1;
	}

	/*
	 * updating cities Table with INNER join query in error returns 0 else 1
	 */
	public int OverrideUpdate() {
		try {
			String query = "Update " + SQL_Schema.Cities.TABLE_NAME
					+ " INNER JOIN citiesUpdate ON "
					+ SQL_Schema.Cities.TABLE_NAME + "."
					+ SQL_Schema.Cities.CountryID
					+ "=citiesUpdate.countryID and "
					+ SQL_Schema.Cities.TABLE_NAME + "."
					+ SQL_Schema.Cities.CityName + "=citiesUpdate.city_name"
					+ " SET " + SQL_Schema.Cities.TABLE_NAME + "."
					+ SQL_Schema.Cities.Lon + "=citiesUpdate.longitude, "
					+ SQL_Schema.Cities.TABLE_NAME + "."
					+ SQL_Schema.Cities.Lat + "=citiesUpdate.latitude;";
			stmt.execute(query);
		} catch (SQLException e) {
			System.out.println("Error");
			return 0;
		}
		return 1;
	}

	/*
	 * updating cities table with insert query this is a nested query that uses
	 * not exists in order to choose what to insert into cities table on error
	 * returns 0 else 1
	 */
	public int updateWithnoOverride() {
		try {
			String query = "insert into " + SQL_Schema.Cities.TABLE_NAME + "("
					+ SQL_Schema.Cities.CountryID + " , "
					+ SQL_Schema.Cities.CityName + " , "
					+ SQL_Schema.Cities.Lat + " , " + SQL_Schema.Cities.Lon
					+ " )" + " SELECT " + SQL_Schema.Cities.CountryID + " , "
					+ SQL_Schema.Cities.CityName + " , "
					+ SQL_Schema.Cities.Lat + " , " + SQL_Schema.Cities.Lon
					+ " From citiesUpdate" + " Where not exists (Select "
					+ SQL_Schema.Cities.CityName + ", "
					+ SQL_Schema.Cities.CountryID + " From  "
					+ SQL_Schema.Cities.TABLE_NAME + " Where "
					+ SQL_Schema.Cities.TABLE_NAME + "."
					+ SQL_Schema.Cities.CityName + "= "
					+ "citiesUpdate.city_name and "
					+ SQL_Schema.Cities.TABLE_NAME + "."
					+ SQL_Schema.Cities.CountryID
					+ "= citiesUpdate.countryID);";
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("Error");
			return 0;

		}
		return 1;
	}

	/*
	 * the function sends drop query for the temporary table citiesUpdate on
	 * error returns 0 else 1
	 */
	public int dropUpdatesTable() {
		try {
			String query = "drop table if exists citiesUpdate;";
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			System.out.println("Error");
			return 0;
		}
		return 1;
	}

	// *******************************update***********************************************

	/*
	 * updating the tables in the data base if flag = 1 override update flag=0
	 * update without override
	 */
	public int updateGeoTables(String continentsCountriesFile,
			String citiesFile, boolean flag, String workspacePath) {
		// the order is important because there is a restrict policy on all the
		// tables
		
			
		LinkedList<Continent> continents = null;
		Map<String, Country> countries = null;
		if (parsing.GeoParser.parserContinentCountryCity(
				continentsCountriesFile, 1) == 0) {
			System.out.println("Error reading from file");
			return 0;
		}
		continents = parsing.GeoParser.continents;
		countries = parsing.GeoParser.countries;

		if ((updateContinentsTable(continents)) == 0) {
			System.out.println("Error");
			return 0;
		}
		continents.clear();
		if ((updateCountriesTable(countries)) == 0) {
			System.out.println("Error");
			return 0;
		}
		countries.clear();
		/* cities update */
		// create File cities1.txt
		String path= workspacePath+ File.separator+ "Cities1.txt";
		if (ParsingCitiesToTextB.CityParser(path, citiesFile) == 0) {
			System.out.println("Error creating file");
			return 0;
		}
	
		// creating Temporary table citiesUpdate table in the DB
		
		if (createUpdateCitiesTable(path) == 0) {
			return 0;
		}
		if (flag) {
			if ((updateAndOverrideCitiesTable()) == 0) {
				System.out.println("Error update with override");
				return 0;
			}
		} else {
			if (updateWithnoOverride() == 0) {
				System.out.println("Error updateWithOutOverride");
				return 0;
			}
		}
		if (dropUpdatesTable() == 0) {
			System.out.println("Error drop updateTable");
			return 0;
		}
		
		
		
		return 1;
	}

	/*
	 * updating table continents on error return 0 otherwise 1
	 */
	public int updateContinentsTable(LinkedList<Continent> continents) {
		// read from the file into JAVA Data structure
		Continent tempContinent = null;
		Continent continentFromFile = null;
		// check if continent exists in the table
		Iterator<Continent> continentIter = continents.iterator();
		// iterating over the list of continents
		while (continentIter.hasNext()) {
			continentFromFile = continentIter.next();
			// tempContinent the result from the continents table
			tempContinent = this.getContinent(continentFromFile
					.getContinentId());
			// if continent does not exists in the database
			if (tempContinent.getContinentId() == (-1)) {
				if (this.addNewContinent(continentFromFile) == 0) {
					System.out.println("Error adding new continents");
					return 0;
				}
			} else
			// else check if it is the same continent
			if (!(continentFromFile.getContinentName().equals(tempContinent
					.getContinentName()))) {
				// update continent name
				if ((this.modifyContinent(continentFromFile,
						continentFromFile.getContinentName()) == 0)) {
					System.out.println("Error updating continent name");
					return 0;
				}
			} else {
				continue;
			}

		}
		return 1;
	}

	/*
	 * updating countries table on error return 0 otherwise 1
	 */
	public int updateCountriesTable(Map<String, Country> countries) {
		// tempCountry is the result from the countries table
		Country tempCountry = null;
		// countryFromFile is the country from the file
		Country countryFromFile = null;
		for (Map.Entry<String, Country> entry : countries.entrySet()) {
			countryFromFile = entry.getValue();
			// countryID is primary key must be unique
			tempCountry = this.getCountry(countryFromFile.getCountryId());
			// if country does not exists in the database
			if (tempCountry.getContinentId() == (-1)) {
				if (this.addNewCountry(countryFromFile) == 0) {
					System.out.println("Error adding new country");
					return 0;
					// check if the name is equal
					// assumption - continents and countries are updated by the
					// same file
					// continents table was updated first there is no
					// continentID missing
					// user can not change continent_id from a country

				} else if (!(tempCountry.equals(countryFromFile))) {
					// check if countryName is different and update
					if (!(tempCountry.getCountryName().equals(countryFromFile
							.getCountryName()))) {
						if ((this.modifyCountry(countryFromFile,
								countryFromFile.getCountryName()) == 0)) {
							System.out.println("Error updating country");
							return 0;
						}
					} else {
						continue;
					}

				}
			}
		}

		return 1;

	}

	
	/**
	 * updating cities table with override- latitude and longitude will be
	 * updated according to the file on error return 0 otherwise return 1
	 */
	public int updateAndOverrideCitiesTable() {

		if (OverrideUpdate() == 0) {
			System.out.println("Error update override");
			return 0;
		}
		if (updateWithnoOverride() == 0) {
			System.out.println("Error update no override");
			return 0;
		}
		return 1;
	}
	
	
	/**
	 * "Update DB" button pressed
	 */
	public static void updateDB(final DB_Abstract myDB,
			final String continentsPath,
			final String citiesPath,
			final boolean override,
			final String workingFolder) {
		
		//show confirmation dialog
		MessageBox confirmMsg = new MessageBox(MainWindow.display.getShells()[0], SWT.OK | SWT.CANCEL);
		confirmMsg.setText("Are You Sure?");
		confirmMsg.setMessage("This action is may be fatal if you do not put the right paths to the right files and folders.");
        int confirmResponse = confirmMsg.open();
        switch(confirmResponse) {
          case SWT.OK:

        	  //thread the update function with paths and flag
        	  new Thread(new Runnable() {
        		  public void run() {
        			  
        			  //this is where sh*t gets real
        			  
        			  int result = myDB.updateGeoTables(
        					  continentsPath,
        					  citiesPath,
        					  override,
        					  workingFolder);
        			  
        			  //Log Message when finished
        			  if (result == 0) {
        				  MainWindow.display.asyncExec(new Runnable() {
	        				  public void run() {
	        					  Logs.append("ERROR UPDATING DB!!!");
	        				  }
	        			  });
        			  } else {
	        			  MainWindow.display.asyncExec(new Runnable() {
	        				  public void run() {
	        					  Logs.append("DB UPDATE FINISHED SUCCESSFULLY!!!");
	        				  }
	        			  });
        			  }
        			  
        		  }
        	  }).start();
        	  
        	  Logs.append("DB update started...");

        	  //break;
          case SWT.CANCEL:
        	  //nothing
        }
		
	}

}